﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;
using System.IO;
using SiteParser.Core.Logging;

namespace SiteParser.Console.SitesConfig.SaksSite.DataEntities
{
	public class ProductInsertParameters
	{
		public string Name { get; set; }
		public string Description { get; set; }
		public string ShortDescription { get; set; }

		public uint[] Categories { get; set; }
		public int Quantity { get; set; }
		public string[] Images { get; set; }
		public string BaseImage { get; set; }
		public string SmallImage { get; set; }
		public string Thumbnail { get; set; }
		public int Price { get; set; }
	}

	public class ProductDataProvider
	{
		public uint InsertProduct(MySqlConnection mySqlConnection, ProductInsertParameters insertParams) {
			var catalogProductEntity = new CatalogProductEntity() {
				AttributeSetID = DBConstants.PRODUCT_DEFAULT_ATTRIBUTE_SET_ID,
				CategoryIDS = insertParams.Categories != null ? string.Join(",", insertParams.Categories.Select(x => x.ToString()).ToArray()) : string.Empty,
				EntityTypeID = DBConstants.PRODUCT_ENTITY_TYPE_ID,
				SkU = Guid.NewGuid().ToString(),
				TypeID = DBConstants.PRODUCT_SIMPLE_TYPE_ID
			};

			uint productId = InsertProductEntity(mySqlConnection, catalogProductEntity);

			foreach (uint categoryId in insertParams.Categories) {
				var categoryProduct = new CatalogCategoryProduct() {
					ProductID = productId,
					CategoryID = categoryId,
					Position = 0
				};
				InsertProductCategory(mySqlConnection, categoryProduct);

				var categoryProductIndex = new CatalogCategoryProductIndex() {
					Visibility = DBConstants.PRODUCT_CATALOG_SEARCH_VISIBILITY_VALUE,
					StoreID = DBConstants.PRODUCT_CATALOG_PRODUCT_STORE_ID,
					CategoryID = categoryId,
					ProductID = productId,
					IsParent = 1
				};
				InsertProductCategoryIndex(mySqlConnection, categoryProductIndex);
			}

			//Add product site
			var productSite = new CatalogProductWebsite() {
				WebsiteID = DBConstants.WEB_SITE_ID,
				ProductID = productId
			};
			InsertProductSite(mySqlConnection, productSite);

			//Add price for product
			var producePrice = new CatalogProductEntityDecimal() {
				AttributeID = DBConstants.PRODUCT_PRICE_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = insertParams.Price
			};
			InsertProductDecimal(mySqlConnection, producePrice);

			//Add weight
			var productWeight = new CatalogProductEntityDecimal() {
				AttributeID = DBConstants.PRODUCT_WEIGTH_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = DBConstants.PRODUCT_DEFAULT_WEIGHT
			};
			InsertProductDecimal(mySqlConnection, productWeight);

			//Add name
			var productName = new CatalogProductEntityVArchAR() {
				AttributeID = DBConstants.PRODUCT_NAME_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = insertParams.Name
			};
			InsertVarCharEntity(mySqlConnection, productName);

			//Add status
			var status = new CatalogProductEntityInt() {
				AttributeID = DBConstants.PRODUCT_STATUS_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = DBConstants.PRODUCT_ENABLED_STATUS_VALUE
			};
			InsertIntEntity(mySqlConnection, status);

			//Add tax class
			var taxClass = new CatalogProductEntityInt() {
				AttributeID = DBConstants.PRODUCT_TAX_CLASS_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = DBConstants.PRODUCT_TAX_CLASS_DEFAULT_VALUE
			};
			InsertIntEntity(mySqlConnection, taxClass);

			//Add visibility
			var visibility = new CatalogProductEntityInt() {
				AttributeID = DBConstants.PRODUCT_VISIBILITY_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = DBConstants.PRODUCT_CATALOG_SEARCH_VISIBILITY_VALUE
			};
			InsertIntEntity(mySqlConnection, visibility);

			//Add description
			var description = new CatalogProductEntityText() {
				AttributeID = DBConstants.PRODUCT_DESCRIPTION_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = insertParams.Description
			};
			InsertProductText(mySqlConnection, description);

			//Add short description
			var shortDescription = new CatalogProductEntityText() {
				AttributeID = DBConstants.PRODUCT_SHORT_DESCRIPTION_ATTRIBUTE_ID,
				EntityID = productId,
				EntityTypeID = catalogProductEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = insertParams.ShortDescription
			};
			InsertProductText(mySqlConnection, shortDescription);

			AddImagesToProduct(catalogProductEntity, insertParams, mySqlConnection);

			Logger.Info(string.Format("Product '{0}' is inserted", insertParams.Name));

			return catalogProductEntity.EntityID;
		}

		#region Private helpers

		private void AddImagesToProduct(CatalogProductEntity productEntity, ProductInsertParameters insertParams, MySqlConnection mySqlConnection) {
			var baseImage = new CatalogProductEntityVArchAR()
			{
				AttributeID = DBConstants.PRODUCT_IMAGE_ATTRIBUTE_ID,
				EntityID = productEntity.EntityID,
				EntityTypeID = productEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = CopyImageToGalery(productEntity.EntityTypeID, insertParams.BaseImage)
			};
			InsertVarCharEntity(mySqlConnection, baseImage);

			var smallImage = new CatalogProductEntityVArchAR()
			{
				AttributeID = DBConstants.PRODUCT_SMALL_IMAGE_ATTRIBUTE_ID,
				EntityID = productEntity.EntityID,
				EntityTypeID = productEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = CopyImageToGalery(productEntity.EntityTypeID, insertParams.SmallImage)
			};
			InsertVarCharEntity(mySqlConnection, smallImage);

			var thumbnail = new CatalogProductEntityVArchAR()
			{
				AttributeID = DBConstants.PRODUCT_THUMBNAIL_ATTRIBUTE_ID,
				EntityID = productEntity.EntityID,
				EntityTypeID = productEntity.EntityTypeID,
				StoreID = DBConstants.DEFAULT_STORE_ID,
				Value = CopyImageToGalery(productEntity.EntityTypeID, insertParams.Thumbnail)
			};
			InsertVarCharEntity(mySqlConnection, thumbnail);

			if (insertParams.Images != null) {
				foreach (var image in insertParams.Images) {
					var galeryEntity = new CatalogProductEntityMediaGallery()
					{
						AttributeID = DBConstants.PRODUCT_MEDIA_GALERY_ATTRIBUTE_ID,
						EntityID = productEntity.EntityID,
						Value = CopyImageToGalery(productEntity.EntityTypeID, image)
					};
					InsertMediaGaleryEntity(mySqlConnection, galeryEntity);
				}
			}
		}

		private string CopyImageToGalery(ushort entityTypeId, string imagePath) {
			if (File.Exists(imagePath)) {
				string imageFileName = Path.GetFileName(imagePath);
				string magentoRelativePath = string.Format("/{0}/{1}/{2}", 0, entityTypeId, imageFileName);
				string relativePath = string.Format("{0}\\{1}\\{2}", 0, entityTypeId, imageFileName);

				string magentoFilePath = Path.Combine(DBConstants.GALERY_IMAGE_PATH, relativePath);
				string imageParentDir = Path.GetDirectoryName(magentoFilePath);

				if (!Directory.Exists(imageParentDir)) {
					try {
						Directory.CreateDirectory(imageParentDir);
					}
					catch (Exception exc) {
						Logger.Error(string.Format("Unable to create directory '{0}'. {1}", imageParentDir,  exc));
					}
				}

				try {
					File.Copy(imagePath, magentoFilePath, true);
				}
				catch (Exception exc) {
					Logger.Error(string.Format("Unable to copy file '{0}' to '{1}'. {2}", imagePath, magentoFilePath, exc));
				}

				return magentoRelativePath;
			}

			return string.Empty;
		}

		#endregion Private helpers

		#region Sql temp methods to solve the problem with "Invalid string format" exception

		private uint InsertProductEntity(MySqlConnection mySqlConnection, CatalogProductEntity productEntity) {
			MySqlCommand insertCommand = new MySqlCommand();
			insertCommand.CommandType = System.Data.CommandType.Text;
			insertCommand.Connection = mySqlConnection;

			if (mySqlConnection.State != System.Data.ConnectionState.Open) {
				mySqlConnection.Open();
			}

			insertCommand.CommandText = string.Format(string.Join(string.Empty, new string[] {
				"INSERT INTO catalog_product_entity",
				"(entity_type_id, attribute_set_id, type_id, sku, category_ids, has_options, required_options)",
				"VALUES ({0}, {1}, '{2}', '{3}', '{4}', {5}, {6});", 
				"SELECT LAST_INSERT_ID()"}),
				productEntity.EntityTypeID, productEntity.AttributeSetID, productEntity.TypeID, productEntity.SkU,
				productEntity.CategoryIDS, productEntity.HasOptions,
				productEntity.RequiredOptions);

			productEntity.EntityID = (uint)(long)insertCommand.ExecuteScalar();

			return productEntity.EntityID;
		}

		private void InsertProductDecimal(MySqlConnection mySqlConnection, CatalogProductEntityDecimal decimalEntity) {
			MySqlCommand insertCommand = new MySqlCommand();
			insertCommand.CommandType = System.Data.CommandType.Text;
			insertCommand.Connection = mySqlConnection;

			if (mySqlConnection.State != System.Data.ConnectionState.Open) {
				mySqlConnection.Open();
			}

			insertCommand.CommandText = string.Format(string.Join(string.Empty, new string[] {
				"INSERT INTO catalog_product_entity_decimal",
				"(entity_type_id, attribute_id, store_id, entity_id, value)",
				"VALUES ({0}, {1}, {2}, {3}, {4})" }),
				decimalEntity.EntityTypeID, decimalEntity.AttributeID, decimalEntity.StoreID, decimalEntity.EntityID, decimalEntity.Value);

			insertCommand.ExecuteNonQuery();
		}

		private void InsertVarCharEntity(MySqlConnection mySqlConnection, CatalogProductEntityVArchAR varcharEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_entity_varchar ",
					"(entity_type_id, attribute_id, store_id, entity_id, value)",
					"VALUES ({0},{1},{2},{3},\"{4}\")"}), varcharEntity.EntityTypeID, varcharEntity.AttributeID,
					varcharEntity.StoreID, varcharEntity.EntityID, Regex.Escape(varcharEntity.Value));

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertIntEntity(MySqlConnection mySqlConnection, CatalogProductEntityInt intEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_entity_int ",
					"(entity_type_id, attribute_id, store_id, entity_id, value)",
					"VALUES ({0},{1},{2},{3},{4})"}), intEntity.EntityTypeID, intEntity.AttributeID,
					intEntity.StoreID, intEntity.EntityID, intEntity.Value);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertEnabledIndex(MySqlConnection mySqlConnection, CatalogProductEnabledIndex enabledIndex) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_enabled_index ",
					"(product_id, store_id, visibility)",
					"VALUES ({0},{1},{2})"}), 
					enabledIndex.ProductID, enabledIndex.StoreID, enabledIndex.Visibility);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertProductText(MySqlConnection mySqlConnection, CatalogProductEntityText textEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_entity_text ",
					"(entity_type_id, attribute_id, store_id, entity_id, value)",
					"VALUES ({0},{1},{2},{3},'{4}')"}), textEntity.EntityTypeID, textEntity.AttributeID,
					textEntity.StoreID, textEntity.EntityID, textEntity.Value);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertProductCategory(MySqlConnection mySqlConnection, CatalogCategoryProduct categoryProduct) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_category_product",
					"(category_id, product_id, position)",
					"VALUES ({0},{1},{2})"}), categoryProduct.CategoryID, categoryProduct.ProductID, categoryProduct.Position);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertProductCategoryIndex(MySqlConnection mySqlConnection, CatalogCategoryProductIndex categoryProductIndex) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_category_product_index",
					"(category_id, product_id, position, is_parent, store_id, visibility)",
					"VALUES ({0},{1},{2}, {3}, {4}, {5})"}), categoryProductIndex.CategoryID, 
					categoryProductIndex.ProductID, categoryProductIndex.Position,
					categoryProductIndex.IsParent, categoryProductIndex.StoreID, categoryProductIndex.Visibility);

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertProductSite(MySqlConnection mySqlConnection, CatalogProductWebsite productSite) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_website",
					"(product_id, website_id)",
					"VALUES ({0},{1})"}), productSite.ProductID, productSite.WebsiteID);
					

				sqlCommand.ExecuteNonQuery();
			}
		}

		private void InsertMediaGaleryEntity(MySqlConnection mySqlConnection, CatalogProductEntityMediaGallery galeryEntity) {
			if (mySqlConnection != null) {
				if (mySqlConnection.State != System.Data.ConnectionState.Open) {
					mySqlConnection.Open();
				}

				MySqlCommand sqlCommand = new MySqlCommand();
				sqlCommand.Connection = mySqlConnection;
				sqlCommand.CommandType = System.Data.CommandType.Text;
				sqlCommand.CommandText = string.Format(string.Join(string.Empty, new string[]{
					"INSERT INTO catalog_product_entity_media_gallery",
					"(attribute_id, entity_id, value)",
					"VALUES ({0},{1},'{2}')"}), galeryEntity.AttributeID, galeryEntity.EntityID, galeryEntity.Value);

				sqlCommand.ExecuteNonQuery();
			}
		}

		#endregion


	}
}
